# -*- coding: utf-8 -*-
import pymysql
import pymysql.cursors
import logging
from twisted.enterprise import adbapi

logger = logging.getLogger(__name__)

# 数据持久化
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
# useful for handling different item types with a single interface
class ArticleSpiderPipeline:
    def process_item(self, item, spider):
        return item


# 将爬取到的数据以异步的方式存入数据库
class AsyInsertMysqlPipeline(object):
    """
    通过twisted插件实现异异步添加数据到MYSQL
    """

    def __init__(self, dbpoll):
        self.dbpoll = dbpoll

    @classmethod
    def from_settings(cls, settings):
        db_params = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            passwd=settings["MYSQL_PASSWORD"],
            charset=settings["MYSQL_CHARSET"],
            cursorclass=pymysql.cursors.DictCursor,
            use_unicode=True,
        )
        db_poll = adbapi.ConnectionPool("pymysql", **db_params)
        # 通过cls()实例化一个MysqlAsynchronousPipeline对象
        return cls(db_poll)

    def process_item(self, item, spider):
        query = self.dbpoll.runInteraction(self.do_insert, item)
        query.addErrback(self.handle_error, item, spider)

    def handle_error(self, failure, item, spider):
        logger.info('异步插入item到mysql失败')

    def do_insert(self, cursor, item):
        params = list()
        if "mc_gysb" == item.get("category", ""):
            sql = """
                  INSERT INTO mc_gysb(company_info_url,name,phone,cz,address,zyyw,ywfw) VALUES(%s,%s,%s,%s,%s,%s,%s) 
                  ON DUPLICATE KEY UPDATE company_info_url=VALUES(company_info_url)
                  """
            params.append(item.get("company_info_url", ""))
            params.append(item.get("name", ""))
            params.append(item.get("phone", ""))
            params.append(item.get("cz", ""))
            params.append(item.get("address", ""))
            params.append(item.get("zyyw", ""))
            params.append(item.get("ywfw", ""))
        else:
            sql = """
                INSERT INTO cnblogs(article_url,title,content,create_time) VALUES(%s,%s,%s,%s) 
                ON DUPLICATE KEY UPDATE title=VALUES(title)
            """
            params.append(item.get("article_url", ""))
            params.append(item.get("title", ""))
            params.append(item.get("content", ""))
            params.append(item.get("create_time", ""))
        # 持久化
        cursor.execute(sql, tuple(params))
